<?php if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class Payment_model extends CI_Model {
	public function __construct() {
		parent::__construct();
	}

	public function get_active(){
      $sql = "SELECT account_id, acct_num, acct_name FROM ar_account WHERE status != 'PND' ";
      $query = $this->db->query($sql);
      return $query->result();
    }

	public function getreceiptnum($val) {
		//$this->db->query("call ar_get_receipt_num('".$val."',@val)");
		$que = $this->db->query("SELECT get_currval('".$val."') AS val");
		return $que->result();
	}
	public function search_acct($acct_num, $acct_name) {
		$sql;
		if ($acct_num != "" && $acct_name != "") {
			$sql = "SELECT * FROM ubs_invoices_v WHERE account_num LIKE " . $this->db->escape($acct_num) . " AND acct_name LIKE '" . $acct_name . "' ";
		} else if ($acct_num == "" && $acct_name != "") {
			$sql = "SELECT * FROM ubs_invoices_v WHERE acct_name LIKE " . $this->db->escape($acct_name) . " ";
		} else {
			$sql = "SELECT * FROM ubs_invoices_v WHERE account_num LIKE " . $this->db->escape($acct_num) . " ";
		}
		$query = $this->db->query('SELECT * FROM ubs_invoices_v WHERE customer_id = "'.$acct_num.'" ');
		if ($query->num_rows() != 0) {
			return $query->result();
		} else {
			$result = "No results found.";
			return $result;
		}
	}

	public function search($acct_num, $acct_name) {
		$sql;
		if ($acct_num != "" && $acct_name != "") {
			$sql = "SELECT * FROM ar_payments_v WHERE acct_num LIKE " . $this->db->escape($acct_num) . " AND acct_name LIKE '" . $acct_name . "' ";
		} else if ($acct_num == "" && $acct_name != "") {
			$sql = "SELECT * FROM ar_payments_v WHERE acct_name LIKE " . $this->db->escape($acct_name) . " ";
		} else {
			$sql = "SELECT * FROM ar_payments_v WHERE acct_num LIKE " . $this->db->escape($acct_num) . " ";
		}

		$query = $this->db->query($sql);
		if ($query->num_rows() != 0) {
			return $query->result();
		} else {
			$result = "No results found.";
			return $result;
		}
	}

	public function get_bal($id) {
		$this->db->where('invoice_id', $id);
		$query = $this->db->get('ubs_invoices_v');
		return $query->result();
	}

	public function insertResibo($data){
		$this->db->query("call ar_receipt_ins(?,?,?,?,?,?,?,?,@id,@err,@msg)", $data);
		$que = $this->db->query("SELECT @id as id,@err as err,@msg as msg");
		$id;
		$msg;
		$err;
		foreach ($que->result() as $keys) {
			$msg = $keys->msg;
			$err = $keys->err;
			$id = $keys->id;
		}
		$arr = array(
			'msg' => $msg,
			'err' => $err,
			'id' => $id,
		);
		echo json_encode($arr);
	}

	public function insertPayments($data) {
		$this->db->query("call ar_payments_ins(?,?,?,?,?,?,?,?,?,?,?,@id,@err,@msg)", $data);
		$que = $this->db->query("SELECT @id as id,@err as err,@msg as msg");
		$id;
		$msg;
		$err;
		foreach ($que->result() as $keys) {
			$msg = $keys->msg;
			$err = $keys->err;
			$id = $keys->id;
		}
		$arr = array(
			'msg' => $msg,
			'err' => $err,
			'id' => $id,
		);
		echo json_encode($arr);
	}

	public function invoicebalupd($id, $data) {
		$this->db->where('invoice_id', $id);
		$this->db->update('ubs_invoices', $data);
		//var_dump($data);
	}

	public function paymentdtl($id) {
		$this->db->where('payment_id', $id);
		$query = $this->db->get('ar_payments');
		return $query->result();
	}

	public function getrec($id) {
		$this->db->where('receipt_number', $id);
		$query = $this->db->get('ar_payments_v');
		return $query->result();
	}

	public function genAdv($id){
		$this->db->where('receipt_number', $id);
		$query = $this->db->get('ar_receipt_applications_v');
		return $query->result();
	}

	public function getrec2($id){
       $this->db->query('SELECT get_nextval("RECEIPT_NUMBER")');

		return $this->db->query('SELECT aa.`acct_num`,
	       ar.`receipt_number`,
	       ar.`payment_amt`,
	       ar.`account_id`,
	       ar.payment_type,
	       ar.`reference`,
	       ar.`ref_no`,
	       ar.`remarks`,
	       ar.`receipt_date`,
	       ar.`selected_amt`
	 FROM ar_receipt ar
		LEFT JOIN ar_account aa ON ar.`account_id` = aa.`account_id` WHERE ar.receipt_number = '.$id)->result();
	}
}